/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IAdresseDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Adresse;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcAdresseDao implements IAdresseDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcAdresseDao.class);
    private static final IConnectionMySQL manager = new ConnectionManager();

    /**
     *
     * @param adresse {
     * @see Adresse}
     * @throws DAOException
     */
    @Override
    public void saveAdresse(Adresse adresse) throws DAOException {
        if (null == adresse) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("INSERT INTO adresse (idBase, numVoie, nomVoie, complement, codePostal, ville, pays, mail, tel, defaut, dateCreation) ");
        builder.append("VALUES (?, AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), AES_ENCRYPT(?,?), ?, NOW())");

        String request = builder.toString();
        logger.debug("Insert client: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, adresse.getIdBase());
            ps.setString(2, adresse.getNumVoie());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, adresse.getNomVoie());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, adresse.getComplement());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, adresse.getCodePostal());
            ps.setString(9, manager.getKeyAES());
            ps.setString(10, adresse.getVille());
            ps.setString(11, manager.getKeyAES());
            ps.setString(12, adresse.getPays());
            ps.setString(13, manager.getKeyAES());
            ps.setString(14, adresse.getMail());
            ps.setString(15, manager.getKeyAES());
            ps.setString(16, adresse.getTel());
            ps.setString(17, manager.getKeyAES());
            ps.setBoolean(18, adresse.isDefaut());
            ps.executeUpdate();

        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            adresse.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @throws DAOException
     */
    @Override
    public void deleteAdresse(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM adresse WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Delete adress: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete adress with id.");
            throw new DAOException("Can not delete adresse with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param adresse
     * @throws DAOException
     */
    @Override
    public void updateAdresse(Adresse adresse) throws DAOException {
        if (null == adresse) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE adresse SET ");
        builder.append("numVoie = AES_ENCRYPT(?,? ),");
        builder.append("nomVoie = AES_ENCRYPT(?,? ),");
        builder.append("complement = AES_ENCRYPT(?,? ),");
        builder.append("codePostal = AES_ENCRYPT(?,? ),");
        builder.append("ville = AES_ENCRYPT(?,? ),");
        builder.append("pays = AES_ENCRYPT(?,? ),");
        builder.append("mail = AES_ENCRYPT(?,? ),");
        builder.append("tel = AES_ENCRYPT(?,? ), ");
        builder.append("defaut = ?, dateModification = NOW(), ");        
        builder.append("idBase = ? ");
        builder.append(" WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Update adress: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, adresse.getNumVoie());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, adresse.getNomVoie());
            ps.setString(4, manager.getKeyAES());
            ps.setString(5, adresse.getComplement());
            ps.setString(6, manager.getKeyAES());
            ps.setString(7, adresse.getCodePostal());
            ps.setString(8, manager.getKeyAES());
            ps.setString(9, adresse.getVille());
            ps.setString(10, manager.getKeyAES());
            ps.setString(11, adresse.getPays());
            ps.setString(12, manager.getKeyAES());
            ps.setString(13, adresse.getMail());
            ps.setString(14, manager.getKeyAES());
            ps.setString(15, adresse.getTel());
            ps.setString(16, manager.getKeyAES());
            ps.setBoolean(17, adresse.isDefaut());
            ps.setInt(18, adresse.getIdBase());
            ps.setInt(19, adresse.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.", e);
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @return
     * @throws DAOException
     */
    @Override
    public Adresse findAdresseById(int id) throws DAOException {
        
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idBase, idAppli, AES_DECRYPT(numVoie,?) AS numVoie,");
        builder.append("AES_DECRYPT(nomVoie,?) AS nomVoie,");
        builder.append("AES_DECRYPT(complement,?) AS complement, AES_DECRYPT(codePostal,?) AS codePostal,");
        builder.append("AES_DECRYPT(ville,?) AS ville, AES_DECRYPT(pays,?) AS pays,");
        builder.append("AES_DECRYPT(mail,?) AS mail, AES_DECRYPT(tel,?) AS tel, ");
        builder.append("defaut FROM adresse WHERE idAppli = ?");
        
        String request = builder.toString();
        logger.debug("select adresse: {}", request);
        
        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            for(int i=1; i<9; i++){
                ps.setString(i, manager.getKeyAES());
            }
            ps.setInt(9, id);
            ps.executeQuery();
   
        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Adresse> adresses = null;
        try {
            adresses = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == adresses || adresses.isEmpty()) {
            throw new NoResultException("No adresse found with id " + id);
        }
        if (adresses.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return adresses.get(0);
    }

    private List<Adresse> map(ResultSet rst) throws SQLException {
        List<Adresse> adresses = new ArrayList<>();

        while (rst.next()) {
            Adresse adresse = new Adresse();
            adresse.setIdAppli(rst.getInt("idAppli"));
            adresse.setIdBase(rst.getInt("idBase"));
            adresse.setNumVoie(rst.getString("numVoie"));
            adresse.setNomVoie(rst.getString("nomVoie"));
            adresse.setComplement(rst.getString("complement"));
            adresse.setCodePostal(rst.getString("codePostal"));
            adresse.setVille(rst.getString("ville"));
            adresse.setPays(rst.getString("pays"));
            adresse.setMail(rst.getString("mail"));
            adresse.setTel(rst.getString("tel"));
            adresse.setDefaut(rst.getBoolean("defaut"));
            adresses.add(adresse);
        }

        return adresses;
    }
}
